package com.ph.irp.base.settlement.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.ph.irp.base.settlement.dao.PBaseSettlementDAO;
import com.ph.irp.model.PBaseSettlement;
import com.ph.util.DBUtil;

public class PBaseSettlementDAOImpl implements PBaseSettlementDAO {
	// dataSource is a tool that we use to connect the database
	private ComboPooledDataSource dataSource;

	private List<PBaseSettlement> settlementList;

	private PBaseSettlement pbaseSettlementModel = null;

	public void setDataSource(ComboPooledDataSource dataSource) throws Throwable{

		this.dataSource = dataSource;
	}
	/**
	 * 新增结算方式
	 */
	@Override
	public void addSettlement(PBaseSettlement pbaseSettlement) throws Throwable{
		// TODO Auto-generated method stub
		
		// 数据库连接
		Connection con = null;
		// 预备语句
		PreparedStatement statement = null;
		// 结果集

		try {
			// 由数据库获得连接
			con = this.dataSource.getConnection();

			statement = con
					.prepareStatement("insert into p_base_settlement(settlementCode,settlementName,accountbookId) values(?,?,?)");
			// 给宿主变量绑定值
			statement.setString(1, pbaseSettlement.getSettlementCode());
			statement.setString(2, pbaseSettlement.getSettlementName());
			statement.setInt(3, pbaseSettlement.getPBaseAccountbookId());
			// 执行预备语句，得到结果集
			statement.executeUpdate();

		} catch (Exception e) {// 捕捉异常并进行处理
			// TODO Auto-generated catch block
			throw new Exception(e.getMessage());
		} finally {// 关闭数据库连接
			try {
				DBUtil.setNullObject(con, statement, null);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				throw new Exception(e.getMessage());
			}
		}
		
	}
	/**
	 * 删除结算方式-可以删除多条
	 */
	@Override
	public void deleteSettlementsById(Object[] settlementIds)throws Throwable {
		// TODO Auto-generated method stub
		// 循环变量
		int loop = 0;
		//
		int settlementId = 0;
		// 数据库连接
		Connection con = null;
		// 预备语句
		PreparedStatement statement = null;
		// 结果集

		try {
			// 由数据库获得连接
			con = this.dataSource.getConnection();
			for (loop = 0; loop < settlementIds.length; loop++) {
				
				statement = con.prepareStatement("delete from p_base_settlement where settlementId=?");
				settlementId = Integer.valueOf((String) settlementIds[loop]);
				// 给宿主变量绑定值
				statement.setInt(1, settlementId);
				statement.executeUpdate();
			}
		} catch (Exception e) {// 捕捉异常并进行处理
			// TODO Auto-generated catch block
			throw new Exception(e.getMessage());
		} finally {// 关闭数据库连接
			try {
				DBUtil.setNullObject(con, statement, null);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				throw new Exception(e.getMessage());
			}
		}
		
	}

	/**
	 * 查询所有的结算方式
	 */
	@Override
	public List<PBaseSettlement> getAllSettlements() throws Throwable{
		// TODO Auto-generated method stub

		settlementList = new ArrayList<PBaseSettlement>();

		// 数据库连接
		Connection con = null;
		// 预备语句
		PreparedStatement statement = null;
		// 结果集
		ResultSet resultSet = null;

		try {
			// 由数据库获得连接
			con = this.dataSource.getConnection();

			statement = con.prepareStatement("select * from p_base_settlement");
			// 执行预备语句，得到结果集
			resultSet = statement.executeQuery();

			turnResultSetToList(settlementList, resultSet);

		} catch (Exception e) {// 捕捉异常并进行处理
			// TODO Auto-generated catch block
			throw new Exception(e.getMessage());
		} finally {// 关闭数据库连接

			try {
				DBUtil.setNullObject(con, statement, resultSet);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				throw new Exception(e.getMessage());
			}
		}

		return settlementList;
	}

	/**
	 * 根据code模糊查询结算方式
	 */
	@Override
	public List<PBaseSettlement> getSettlementsByCode(String settlementCode)throws Throwable {
		// TODO Auto-generated method stub
		settlementList = new ArrayList<PBaseSettlement>();

		// 数据库连接
		Connection con = null;
		// 预备语句
		PreparedStatement statement = null;
		// 结果集
		ResultSet resultSet = null;

		try {
			// 由数据库获得连接
			con = this.dataSource.getConnection();

			statement = con
					.prepareStatement("select * from p_base_settlement where settlementCode like ?");
			// 给宿主变量绑定值
			statement.setString(1, "%"+settlementCode+"%");
			// 执行预备语句，得到结果集
			resultSet = statement.executeQuery();

			turnResultSetToList(settlementList, resultSet);

		} catch (Exception e) {// 捕捉异常并进行处理
			// TODO Auto-generated catch block
			throw new Exception(e.getMessage());
		} finally {// 关闭数据库连接

			try {
				DBUtil.setNullObject(con, statement, resultSet);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				throw new Exception(e.getMessage());
			}
		}
		return settlementList;
	}
	/**
	 * 根据name模糊查询结算方式
	 */
	@Override
	public List<PBaseSettlement> getSettlementsByName(String settlementName)throws Throwable {
		// TODO Auto-generated method stub

		settlementList = new ArrayList<PBaseSettlement>();

		// 数据库连接
		Connection con = null;
		// 预备语句
		PreparedStatement statement = null;
		// 结果集
		ResultSet resultSet = null;

		try {
			// 由数据库获得连接
			con = this.dataSource.getConnection();

			statement = con
					.prepareStatement("select * from p_base_settlement where settlementName like ?");
			// 给宿主变量绑定值
			statement.setString(1, "%"+settlementName+"%");
			// 执行预备语句，得到结果集
			resultSet = statement.executeQuery();

			turnResultSetToList(settlementList, resultSet);

		} catch (Exception e) {// 捕捉异常并进行处理
			// TODO Auto-generated catch block
			throw new Exception(e.getMessage());
		} finally {// 关闭数据库连接

			try {
				DBUtil.setNullObject(con, statement, resultSet);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				throw new Exception(e.getMessage());
			}
		}
		return settlementList;
	}
	/**
	 * 根据name和code模糊查询结算方式
	 */
	@Override
	public List<PBaseSettlement> getSettlementsByNameAndCode(
			String settlementName, String settlementCode) throws Throwable{
		// TODO Auto-generated method stub

		settlementList = new ArrayList<PBaseSettlement>();

		// 数据库连接
		Connection con = null;
		// 预备语句
		PreparedStatement statement = null;
		// 结果集
		ResultSet resultSet = null;

		try {
			// 由数据库获得连接
			con = this.dataSource.getConnection();

			statement = con
					.prepareStatement("select * from p_base_settlement where settlementName like ? and settlementCode like ?");
			// 给宿主变量绑定值
			statement.setString(1, "%"+settlementName+"%");
			statement.setString(2, "%"+settlementCode+"%");
			// 执行预备语句，得到结果集
			resultSet = statement.executeQuery();

			turnResultSetToList(settlementList, resultSet);

		} catch (Exception e) {// 捕捉异常并进行处理
			// TODO Auto-generated catch block
			throw new Exception(e.getMessage());
		} finally {// 关闭数据库连接

			try {
				DBUtil.setNullObject(con, statement, resultSet);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				throw new Exception(e.getMessage());
			}
		}
		return settlementList;

	}
	/**
	 * 根据id查询结算方式
	 */
	@Override
	public PBaseSettlement getSettlementById(Integer settlementId) throws Throwable{
		// TODO Auto-generated method stub

		settlementList = new ArrayList<PBaseSettlement>();

		// 数据库连接
		Connection con = null;
		// 预备语句
		PreparedStatement statement = null;
		// 结果集
		ResultSet resultSet = null;

		try {
			// 由数据库获得连接
			con = this.dataSource.getConnection();

			statement = con
					.prepareStatement("select * from p_base_settlement where settlementId=?");
			// 给宿主变量绑定值
			statement.setInt(1, settlementId);
			// 执行预备语句，得到结果集
			resultSet = statement.executeQuery();

			turnResultSetToList(settlementList, resultSet);

		} catch (Exception e) {// 捕捉异常并进行处理
			// TODO Auto-generated catch block
			throw new Exception(e.getMessage());
		} finally {// 关闭数据库连接

			try {
				DBUtil.setNullObject(con, statement, resultSet);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				throw new Exception(e.getMessage());
			}
		}
		return settlementList.get(0);
	}
	/**
	 * 更新结算方式
	 */
	public void updateSettlement(PBaseSettlement pbaseSettlement) throws Throwable{
		// TODO Auto-generated method stub
		// 数据库连接
		Connection con = null;
		// 预备语句
		PreparedStatement statement = null;

		try {
			// 由数据库获得连接
			con = this.dataSource.getConnection();

			statement = con
					.prepareStatement("update p_base_settlement set settlementCode=? ,settlementName=? ,accountbookId=? where settlementId=?");
			// 给宿主变量绑定值
			statement.setString(1, pbaseSettlement.getSettlementCode());
			statement.setString(2, pbaseSettlement.getSettlementName());
			statement.setInt(3, pbaseSettlement.getPBaseAccountbookId());
			statement.setInt(4, pbaseSettlement.getSettlementId());
			// 执行预备语句，得到结果集
			statement.executeUpdate();

		} catch (Exception e) {// 捕捉异常并进行处理
			// TODO Auto-generated catch block
			throw new Exception(e.getMessage());
		} finally {// 关闭数据库连接
			try {
				DBUtil.setNullObject(con, statement, null);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				throw new Exception(e.getMessage());
			}
		}
		
	}

	/**
	 * 迭代结果集，将数据封装到settlementList中
	 * 
	 * @param settlementList
	 * @param resultSet
	 * @return
	 * @throws Throwable
	 */
	public List<PBaseSettlement> turnResultSetToList(
			List<PBaseSettlement> settlementList, ResultSet resultSet)
			throws Throwable {

		// 迭代结果集
		while (resultSet.next()) {

			pbaseSettlementModel = new PBaseSettlement();

			pbaseSettlementModel.setSettlementId(resultSet
					.getInt("settlementId"));
			pbaseSettlementModel.setPBaseAccountbookId(resultSet
					.getInt("accountbookId"));
			pbaseSettlementModel.setSettlementCode(resultSet
					.getString("settlementCode"));
			pbaseSettlementModel.setSettlementName(resultSet
					.getString("settlementName"));

			settlementList.add(pbaseSettlementModel);
		}

		return settlementList;
	}
}
